package com.shsh.safemanage.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.shsh.safemanage.dao.ConnectionManager;

public class DaoUtil {
	public static String getPagingSQL(String sql, int page, int pageSize, String keyField, boolean asc) {
		int start = pageSize * (page - 1);
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from (select row_number() over(order by ");
		sb.append(keyField);
		if (asc) {
			sb.append(" asc) ");
		} else {
			sb.append(" desc) ");
		}
		sb.append("as rowNum, * from (");
		sb.append(sql);
		sb.append(") as t1) as t where t.rowNum>" + start);
		sb.append(" and t.rowNum<=" + (start+pageSize));
		
		String pagingSQL = sb.toString();
		
		return pagingSQL;
	}
	
	public static String getPagingSQL(String sql, int page, int pageSize, String [] keyFields, Boolean [] ascs) {
		int start = pageSize * (page - 1);
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from (select row_number() over(order by ");
		for (int i = 0; i < keyFields.length; i++) {
			String keyField = keyFields[i];
			Boolean asc = ascs[i];
			if (asc) {
				sb.append(keyField + " asc");
			} else {
				sb.append(keyField + " desc");
			}
			
			if (i < keyFields.length-1) {
				sb.append(",");
			}
		}
		
		sb.append(") ");
		sb.append("as rowNum, * from (");
		sb.append(sql);
		sb.append(") as t1) as t where t.rowNum>" + start);
		sb.append(" and t.rowNum<=" + (start+pageSize));
		
		String pagingSQL = sb.toString();
		
		return pagingSQL;
	}
	
	public static int getCount(String sql, int [] sqlTypes, Object [] objs) {
		if (sqlTypes == null) {
			return 0;
		}
		
		if (objs == null) {
			return 0;
		}
		
		if (sqlTypes.length != objs.length) {
			return 0;
		}
		
		StringBuilder sb = new StringBuilder();
		sb.append("select count(*) from ");
		sb.append("(" + sql + ") as t");
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sb.toString());
			for (int i = 0; i < sqlTypes.length; i++) {
				pstm.setObject(i+1, objs[i], sqlTypes[i]);
			}
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return 0;
	}
}
